create table if not exists jms_dm.dm_star_staff_rnk_detail_dt(
  agent_code           varchar(50) COMMENT '代理code',
  network_code         varchar(50) COMMENT '网点code',
  staff_code           varchar(50) COMMENT '员工code',
  staff_name           varchar(50) COMMENT '员工name',
  network_name         varchar(50) COMMENT '网点name',
  agent_name           varchar(50) COMMENT '代理name',
  date_type            int    COMMENT '日期类型 0:天维度 1:月维度 2:季维度 3:当月累计维度',
  date_time            date   COMMENT '日期时间',
  taking_success_num   bigint COMMENT '揽收成功量',
  taking_fail_num      bigint COMMENT '揽收失败量',
  taking_success_score bigint COMMENT '散单揽收量计分',
  taking_fail_score    bigint COMMENT '取消量计分',
  need_sign_num        bigint COMMENT '时效签收应签收量',
  sign_intime_num      bigint COMMENT '时效签收及时签收量',
  sign_intime_rate     varchar(50)  COMMENT '时效签收准点率',
  sign_intime_score    varchar(50) COMMENT '时效签收准点率得分',
  complaint_num        bigint COMMENT '客诉数量',
  fwtd_num             bigint COMMENT '服务态度投诉次数',
  complaint_score      bigint COMMENT '客诉计分',
  uniform_num          bigint COMMENT '工服抽查次数',
  uniform_score        bigint COMMENT '工服抽查次数计分',
  total_score          varchar(50)  COMMENT '总分',
  star_level           bigint COMMENT '星级',
  network_rank         bigint COMMENT '网点排名',
  agent_rank           bigint COMMENT '代理区排名',
  country_rank         bigint COMMENT '全国排名',
  dispatch_loss_cnt    bigint COMMENT '派送途中遗失数',
  dispatch_loss_score  bigint COMMENT '派送途中遗失分',
  hire_time            varchar(50) COMMENT '入职时间',
  is_new               bigint COMMENT '是否新员工',
  mark_cnt             bigint COMMENT '打标数',
  tel_cnt              bigint COMMENT '电联数',
  mark_tel_rate        varchar(50) COMMENT '打标电联率',
  mark_tel_score       varchar(50) COMMENT '打标电联分',
  question_pass_cnt    bigint COMMENT '开机五道题通过数',
  question_pass_score  bigint COMMENT '开机五道题得分',
  agent_staff_cnt      bigint COMMENT '代理区员工数量',
  country_staff_cnt    bigint COMMENT '全国员工数量',
  next_star_need_score varchar(50)	  COMMENT '下一星级需要分数',
  total_add_score	   varchar(50)	  COMMENT '总扣分',
  total_deduct_score   varchar(50)	  COMMENT '总加分'
)
ENGINE=OLAP
DUPLICATE KEY(agent_code, network_code)
COMMENT "星级排名日月季明细数据"
PARTITION BY RANGE(date_time)
(
    START ("2023-09-01") END ("2024-01-01") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(staff_code) BUCKETS 1
PROPERTIES (
  "replication_num" = "3",
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "DAY",
  "dynamic_partition.time_zone" = "Asia/Shanghai",
  "dynamic_partition.start" = "-185",
  "dynamic_partition.end" = "32",
  "dynamic_partition.prefix" = "p",
  "dynamic_partition.buckets" = "1",
  "in_memory" = "false",
  "storage_format" = "V2",
  "enable_persistent_index" = "false"
);